# -*- coding: utf-8 -*-
# MySQL Workbench Python script
# <description>
# Written in MySQL Workbench 8.0.21

import grt

# import mforms

schema_name = 'schema'

account_name = 'account'
source_group_name = 'source'
destination_group_name = 'destination'


def query(sql, field_names):
    results = grt.root.wb.sqlEditors[0].executeScript(sql)
    if len(results) == 0:
        print("No result: {sql}".format(sql=sql))
        return []

    result_set = results[0]
    rows = []
    for i in range(result_set.rowCount - 1):
        result_set.goToRow(i)
        row = {}
        for field_name in field_names:
            field_value = result_set.stringFieldValueByName(field_name)
            row[field_name] = field_value
        rows.append(row)

    return rows


def query_account_id(account_name):
    result = query("SELECT `_id` FROM `{schema_name}`.`accounts` WHERE `name` = '{account_name}';"
                   .format(schema_name=schema_name, account_name=account_name), ["_id"])
    if len(result) == 1:
        return result[0]["_id"]
    else:
        return None


def query_group_id(group_name):
    result = query("SELECT `_id` FROM `{schema_name}`.`groups` WHERE `name` = '{group_name}';"
                   .format(schema_name=schema_name, group_name=group_name), ["_id"])
    if len(result) == 1:
        return result[0]["_id"]
    else:
        return None


def query_channel(group_id, account_id):
    result = query("SELECT * FROM `{schema_name}`.`channels` WHERE `groupId` = '{group_id}' AND "
                   "`accountId` = '{account_id}';"
                   .format(schema_name=schema_name, group_id=group_id, account_id=account_id),
                   ["groupId", "accountId", "channelName", "channelDetail", "createdAt", "updatedAt"])
    return result


def create_channel(**kwargs):

    sql = 'INSERT INTO `{schema_name}`.`channels` (`groupId`, `accountId`, `channelName`, `channelDetail`, ' \
          '`createdAt`, `updatedAt`) '
    sql += "VALUES ('{group_id}', '{account_id}', '{channel_name}', '{channel_detail}', " \
           "now(), now()); "
    print(sql.format(**kwargs))


# Main logic starts here.

account_id = query_account_id(account_name)
print("Account name: " + account_name + ", ID: " + account_id)
source_group_id = query_group_id(source_group_name)
print("Source group name: " + source_group_name + ", ID: " + source_group_id)
destination_group_id = query_group_id(destination_group_name)
print("Destination group name:" + destination_group_name + ", ID: " + destination_group_id)

destination_account_channels = query_channel(destination_group_id, account_id)

if len(destination_account_channels) > 0:
    print('Existing channels:')
    for channel in destination_account_channels:
        print(channel)

else:
    source_account_channels = query_channel(source_group_id, account_id)

    for channel in source_account_channels:
        create_channel(schema_name=schema_name, group_id=destination_group_id, account_id=account_id,
                       channel_name=channel['channelName'], channel_detail=channel['channelDetail'])
